﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using Liju.Model;

namespace LiJu.Db
{
    public class ProductTaskRecordDbHelper
    {
        /// <summary>
        /// 查询所有产品
        /// </summary>
        /// <returns></returns>
        public static List<ProductTaskRecord> GetAllProductTaskRecords()
        {
            //获取没有删除的产品
            //string strMySql = "select * from producttaskrecord where State!=2  or State is Null";
            string strMySql = "select * from producttaskrecord order by UpdateTime desc";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            var products = DbExtension.DataTableToList<ProductTaskRecord>(dt);
            return products;
        }

        /// <summary>
        /// 查当日产量 0未执行 1 Pass 2 NG 3执行中 4品质PASS 5品质NG 6品质重做
        /// </summary>
        /// <returns></returns>
        public static int GetProductTaskTodayYield()
        {
            string strMySql = "select count(*) from producttaskrecord where (State=1 or State=4) and UpdateTime >= '" + DateTime.Now.Date.ToString("yyyy-MM-dd") + 
                " 00:00:00' and UpdateTime <='" + DateTime.Now.Date.ToString("yyyy-MM-dd") + " 23:59:59'";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            return Convert.ToInt32(dt.Rows[0][0]);
        }

        /// <summary>
        /// 查当日OK NG产量 0未执行 1 Pass 2 NG 3执行中 4品质PASS 5品质NG 6品质重做
        /// </summary>
        /// <returns></returns>
        public static DataTable GetProductTaskTodayOKNG()
        {
            string strMySql = "select "+
                "count(case when (State=1 or State=4) then 1 end) 'AllOK'," +
                "count(case when (State = 2 or State = 5) then 1 end) 'AllNG'," +
                "count(case when (ScrewModel = 1 and(State = 1 or State = 4)) then 1 end) 'OK1'," +
                "count(case when (ScrewModel = 1 and(State = 2 or State = 5)) then 1 end) 'NG1'," +
                "count(case when (ScrewModel = 2 and(State = 1 or State = 4)) then 1 end) 'OK2'," +
                "count(case when (ScrewModel = 2 and(State = 2 or State = 5)) then 1 end) 'NG2' " +
                "from producttaskrecord where UpdateTime >= '" + DateTime.Now.ToString("yyyy-MM-dd") +
                " 00:00:00' and UpdateTime <= '" + DateTime.Now.ToString("yyyy-MM-dd") + " 23:59:59'";
            return DalMySQL.ExecuteDataTable(strMySql);
        }

        /// <summary>
        /// 查产品 第几个螺丝的任务记录 用于中断接着执行此螺丝 state 0未执行 1 Pass 2 NG 3执行中 4品质判定PASS 5品质判定NG 6品质判定重做
        /// </summary>
        /// <param name="ProductRecordID"></param>
        /// <param name="TaskID"></param>
        /// <param name="WorkNo">当前任务号 第几个螺钉 从0开始</param>
        /// <param name="nState"></param>
        /// <returns></returns>
        public static ProductTaskRecord GetProductTaskRecord(Int64 ProductRecordID, Int64 TaskID, Int64 WorkNo, int nState)
        {
            string strMySql = "select * from producttaskrecord where ProductRecordID=" + ProductRecordID + " and TaskID=" + TaskID +
                " and WorkNo=" + WorkNo + " and State=" + nState + " order by UpdateTime desc";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            List<ProductTaskRecord> listProductTaskRecord = DbExtension.DataTableToList<ProductTaskRecord>(dt);
            if (listProductTaskRecord.Count > 0)
            {
                return listProductTaskRecord[0];
            }
            else
            {
                return null;
            }
        }

        /// <summary>
        /// 根据产品SN找其已完成螺丝记录 同螺丝记录取最后一条 true含预拧记录 false不含预拧记录
        /// </summary>
        /// <param name="strProductSN"></param>
        /// <param name="bYuNing">true含预拧记录 false不含预拧记录</param>
        /// <returns></returns>
        public static List<ProductTaskRecord> GetProductTaskRecordList(string strProductSN, bool bYuNing = false)
        {
            string strYuNing = "";
            if (!bYuNing) //true含预拧记录 false不含预拧记录
            {
                strYuNing = "TightenPrepare = 0 and";
            }
            string strMySql = //State 0未执行 1 Pass 2 NG 3执行中 4品质PASS 5品质NG 6品质重做
                $"select * from producttaskrecord " +
                $"where "+ strYuNing + " (state=1 or State=2 or State=4 or State=5) and " +
                $"ProductTaskRecordID in" +
                    "( select max(ProductTaskRecordID) from producttaskrecord " +
                    "  where " + strYuNing + " (state=1 or State=2 or State=4 or State=5) and ProductSN ='" + 
                    strProductSN + "' group by WorkNo ) " +
                "order by ProductTaskRecordID";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            return DbExtension.DataTableToList<ProductTaskRecord>(dt);
        }

        /// <summary>
        /// 添加产品生产记录 返回自增主键ID -1异常
        /// </summary>
        /// <returns></returns>
        public static Int64 ProductTaskRecordAdd(ProductTaskRecord productTaskRecord)
        {
            string strThisTime = DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss");
            string strMySql = "insert into ProductTaskRecord (ProductRecordID, ProductName, ProductSN, TaskID, WorkNo, TaskImgNo, " +
                "State, ScrewDriverParam, ScrewLocation, ScrewLocationReal, SleeveModel, ScrewModel, ScrewNum, GongHao, UserName, " +
                "CreateTime, UpdateTime, TaskPama1, TaskPamaReal1, TaskPama2, TaskPamaReal2, TaskPama3, TaskPamaReal3, NGType, " +
                "NGReMark, NGJudgeGongHao, NGJudgeUserName, NGJudgeTime,TightenPrepare) values(" + 
                productTaskRecord.ProductRecordID + ",'" + productTaskRecord.ProductName + "','" + productTaskRecord.ProductSN + "'," + 
                productTaskRecord.TaskID + "," + productTaskRecord.WorkNo + "," + productTaskRecord.TaskImgNo + "," + 
                productTaskRecord.State + "," + productTaskRecord.ScrewDriverParam + ",'" + productTaskRecord.ScrewLocation + "','" + 
                productTaskRecord.ScrewLocationReal + "'," + productTaskRecord.SleeveModel + "," + productTaskRecord.ScrewModel + "," + 
                productTaskRecord.ScrewNum + ",'" + productTaskRecord.GongHao + "','" + productTaskRecord.UserName + "','" +
                strThisTime + "','" + strThisTime + "','" + productTaskRecord.TaskPama1 + "','" + productTaskRecord.TaskPamaReal1 + "','" +
                productTaskRecord.TaskPama2 + "','" + productTaskRecord.TaskPamaReal2 + "','" + productTaskRecord.TaskPama3 + "','" + 
                productTaskRecord.TaskPamaReal3 + "', '', '', '', '', '" + strThisTime+ "',"+ 
                productTaskRecord.TightenPrepare +");select @@IDENTITY ";
            DataTable dt = DalMySQL.ExecuteDataTable(strMySql);
            if (dt != null && dt.Rows.Count > 0)
            {
                return Convert.ToInt64(dt.Rows[0][0]);
            }
            else
            {
                return -1;
            }
        }

        /// <summary>
        /// 电批执行后 更新产品生产记录
        /// </summary>
        /// <returns></returns>
        public static Int64 ProductTaskRecordUpdate(ProductTaskRecord productTaskRecord)
        {
            string strMySql = "Update ProductTaskRecord set State=" + productTaskRecord.State + 
                ", ScrewLocationReal='" + productTaskRecord.ScrewLocationReal + "', GongHao='" + productTaskRecord.GongHao + 
                "', UserName='" + productTaskRecord.UserName + "', CreateTime='" + productTaskRecord.CreateTime + 
                "', TaskPamaReal1='" + productTaskRecord.TaskPamaReal1 + "', TaskPamaReal2='" + productTaskRecord.TaskPamaReal2 +
                "', TaskPamaReal3='" + productTaskRecord.TaskPamaReal3 + "', UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")+
                "' where ProductTaskRecordID=" + productTaskRecord.ProductTaskRecordID;
            return DalMySQL.ExecuteNonQuery(strMySql);
        }

        /// <summary>
        /// 品质判定NG产品 更新产品生产记录
        /// </summary>
        /// <returns></returns>
        public static Int64 ProductTaskRecordPZUpdate(ProductTaskRecord productTaskRecord)
        {
            string strMySql = "Update ProductTaskRecord set State=" + productTaskRecord.State +
                ", NGJudgeGongHao='" + productTaskRecord.NGJudgeGongHao + "', NGJudgeUserName='" + productTaskRecord.NGJudgeUserName +
                "', NGType='" + productTaskRecord.NGType + "', NGReMark='" + productTaskRecord.NGReMark +
                "', UpdateTime='" + DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss") +
                "' where ProductTaskRecordID=" + productTaskRecord.ProductTaskRecordID;
            return DalMySQL.ExecuteNonQuery(strMySql);
        }
    }
}
